package _8_Gorm框架老郭

import (
	"errors"
	"fmt"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
	"time"
)

// GORM 提供了 First、Take、Last 方法，以便从数据库中检索单个对象。
// 当查询数据库时它添加了 LIMIT 1 条件，且没有找到记录时，它会返回 ErrRecordNotFound 错误
func main() {
	type User struct {
		gorm.Model
		Name     string
		Age      uint
		Birthday time.Time
	}

	dsn := "root:123456@tcp(127.0.0.1:3306)/golang_db?charset=utf8mb4&parseTime=True&loc=Local"
	db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
	if err != nil {
		panic("failed to connect darabase")
	}
	//准备数据
	user := User{Name: "Jinzhu", Age: 18, Birthday: time.Now()}

	// 获取第一条记录（主键升序）
	db.First(&user)
	// SELECT * FROM users ORDER BY id LIMIT 1;

	// 获取一条记录，没有指定排序字段
	db.Take(&user)
	// SELECT * FROM users LIMIT 1;

	// 获取最后一条记录（主键降序）
	db.Last(&user)
	// SELECT * FROM users ORDER BY id DESC LIMIT 1;

	result := db.First(&user)
	fmt.Println(result.RowsAffected) // 返回找到的记录数
	fmt.Println(result.Error)        // returns error or nil

	// 检查 ErrRecordNotFound 错误
	errors.Is(result.Error, gorm.ErrRecordNotFound)
	//如果你想避免ErrRecordNotFound错误，你可以使用Find，比如db.Limit(1).Find(&user)，Find方法可以接受struct和slice的数据。
}

// First 和 Last 会根据主键排序，分别查询第一条和最后一条记录。 只有在目标 struct 是指针或者通过 db.Model() 指定 model 时，该方法才有效。
// 此外，如果相关 model 没有定义主键，那么将按 model 的第一个字段进行排序
func ad() {
	type User struct {
		gorm.Model
		Name     string
		Age      uint
		Birthday time.Time
	}
	var user User
	var users []User

	dsn := "root:123456@tcp(127.0.0.1:3306)/golang_db?charset=utf8mb4&parseTime=True&loc=Local"
	db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
	if err != nil {
		panic("failed to connect darabase")
	}

	// 有效，因为目标struct 是指针
	db.First(&user)
	// SELECT * FROM `users` ORDER BY `users`.`id` LIMIT 1

	// 有效，因为通过 db.Model 指定了nidel
	result := map[string]interface{}{}
	db.Model(&User{}).First(&result)
	// SELECT * FROM `users` ORDER BY `users`.`id` LIMIT 1

	// 无效
	result1 := map[string]interface{}{}
	db.Table("users").First(&result1)

	// 配合Take有效
	result2 := map[string]interface{}{}
	db.Table("users").Take(&result2)

	// 未指定主键，会根据第一个字段排序(即：code)
	type Language struct {
		Code string
		Name string
	}
	db.First(&Language{})
	// SELECT * FROM `languages` ORDER BY `languages`.`code` LIMIT 1

	//根据主键检索如果主键是数字类型
	db.First(&user, "10")
	// SELECT * FROM users WHERE id = 10;

	db.Find(&users, []int{1, 2, 3})
	// SELECT * FROM users WHERE id IN (1,2,3);

	//如果主键是字符串（例如像 uuid），查询将被写成这样
	db.First(&user, "id = ?", "1b74413f-f3b8-409f-ac47-e8c062e3472a")
	// SELECT * FROM users WHERE id = "1b74413f-f3b8-409f-ac47-e8c062e3472a";

	//String 条件
	// Get first matched record
	db.Where("name = ?", "jinzhu").First(&user)
	// SELECT * FROM users WHERE name = 'jinzhu' ORDER BY id LIMIT 1;

	// Get all matched records
	db.Where("name <> ?", "jinzhu").Find(&users)
	// SELECT * FROM users WHERE name <> 'jinzhu';

	// IN
	db.Where("name IN ?", []string{"jinzhu", "jinzhu 2"}).Find(&users)
	// SELECT * FROM users WHERE name IN ('jinzhu','jinzhu 2');

	// LIKE
	db.Where("name LIKE ?", "%jin%").Find(&users)
	// SELECT * FROM users WHERE name LIKE '%jin%';

	// AND
	db.Where("name = ? AND age >= ?", "jinzhu", "22").Find(&users)
	// SELECT * FROM users WHERE name = 'jinzhu' AND age >= 22;

	// Time
	db.Where("updated_at > ?", "2000-01-01 00:00:00").Find(&users)
	// SELECT * FROM users WHERE updated_at > '2000-01-01 00:00:00';

	// BETWEEN
	db.Where("created_at BETWEEN ? AND ?", "2000-01-01 00:00:00", "2010-01-01 00:00:00").Find(&users)
	// SELECT * FROM users WHERE created_at BETWEEN '2000-01-01 00:00:00' AND '2000-01-08 00:00:00';

	//Struct & Map 条件
	// Struct,只会查询非零字段
	db.Where(&User{Name: "jinzhu", Age: 20}).First(&user)
	// SELECT * FROM users WHERE name = "jinzhu" AND age = 20 ORDER BY id LIMIT 1;

	// Map，可以查询非零字段，{"name": "jinzhu", "age": 0}
	db.Where(map[string]interface{}{"name": "jinzhu", "age": 20}).Find(&users)
	// SELECT * FROM users WHERE name = "jinzhu" AND age = 20;

	// Slice of primary keys
	db.Where([]int64{20, 21, 22}).Find(&users)
	// SELECT * FROM users WHERE id IN (20, 21, 22);

	//指定结构体查询字段
	db.Where(&User{Name: "jinzhu"}, "name", "Age").Find(&users)
	// SELECT * FROM users WHERE name = "jinzhu" AND age = 0;
	db.Where(&User{Name: "jinzhu"}, "Age").Find(&users)
	// SELECT * FROM users WHERE age = 0;

	//内联条件
	// 根据主键获取记录，非零整形主键
	db.First(&user, "id = ?", "string_primary_key")
	// SELECT * FROM users WHERE id = 'string_primary_key';

	// Plain SQL
	db.Find(&user, "name = ?", "jinzhu")
	// SELECT * FROM users WHERE name = "jinzhu";

	db.Find(&users, "name <> ? AND age > ?", "jinzhu", 20)
	// SELECT * FROM users WHERE name <> "jinzhu" AND age > 20;

	// Struct
	db.Find(&users, User{Age: 20})
	// SELECT * FROM users WHERE age = 20;

	// Map
	db.Find(&users, map[string]interface{}{"age": 20})
	// SELECT * FROM users WHERE age = 20;

	// Not语句:用法类似于Where
	db.Not("price between ? and ?", 100, 200).Find(&users)
	// Or语句
	db.Where("price = ?", 200).Or("title like  ?", "红%").Find(&users)

	// 排序
	db.Order("price desc, id").Find(&users)
	// limit
	db.Limit(2).Find(&users)
	// db.Offset(3).Find(&users)
	db.Limit(2).Offset(1).Find(&users)
}
